Imports System.Web

Public Class Professionista
    Inherits Connessione

    Private _id As String
    Private _intest As String
    Private _nome As String
    Private _cognome As String
    Private _sesso As String
    Private _numIsc As String
    Private _luogoN As String
    Private _descComuneN As String
    Private _provN As String
    Private _dataN As String
    Private _codFisc As String
    Private _indRes As String
    Private _capRes As String
    Private _cittaRes As String
    Private _provRes As String
    Private _telefono As String
    Private _collegio As String
    Private _provAlbo As String
    Private _dataIsc As String
    Private _nominativo As String
    Private _cf As String
    Private _indContatto As String
    Private _telContatto As String
    Private _fax As String
    Private _intestRif As String
    Private _nomeRif As String
    Private _cognomeRif As String
    Private _sessoRif As String
    Private _settori As DataTable



    Public Property id() As String
        Get
            Return _id
        End Get
        Set(ByVal Value As String)
            _id = Value
        End Set
    End Property
    Public Property intest() As String
        Get
            Return _intest
        End Get
        Set(ByVal Value As String)
            _intest = Value
        End Set
    End Property
    Public Property nome() As String
        Get
            Return _nome
        End Get
        Set(ByVal Value As String)
            _nome = Value
        End Set
    End Property
    Public Property cognome() As String
        Get
            Return _cognome
        End Get
        Set(ByVal Value As String)
            _cognome = Value
        End Set
    End Property
    Public Property sesso() As String
        Get
            Return _sesso
        End Get
        Set(ByVal Value As String)
            _sesso = Value
        End Set
    End Property
    Public Property numIsc() As String
        Get
            Return _numIsc
        End Get
        Set(ByVal Value As String)
            _numIsc = Value
        End Set
    End Property
    Public Property luogoN() As String
        Get
            Return _luogoN
        End Get
        Set(ByVal Value As String)
            _luogoN = Value
        End Set
    End Property
    Public Property descComuneN() As String
        Get
            Return _descComuneN
        End Get
        Set(ByVal Value As String)
            _descComuneN = Value
        End Set
    End Property
    Public Property provN() As String
        Get
            Return _provN
        End Get
        Set(ByVal Value As String)
            _provN = Value
        End Set
    End Property
    Public Property dataN() As String
        Get
            Return _dataN
        End Get
        Set(ByVal Value As String)
            _dataN = Value
        End Set
    End Property
    Public Property codFisc() As String
        Get
            Return _codFisc
        End Get
        Set(ByVal Value As String)
            _codFisc = Value
        End Set
    End Property
    Public Property indRes() As String
        Get
            Return _indRes
        End Get
        Set(ByVal Value As String)
            _indRes = Value
        End Set
    End Property
    Public Property capRes() As String
        Get
            Return _capRes
        End Get
        Set(ByVal Value As String)
            _capRes = Value
        End Set
    End Property
    Public Property cittaRes() As String
        Get
            Return _cittaRes
        End Get
        Set(ByVal Value As String)
            _cittaRes = Value
        End Set
    End Property
    Public Property provRes() As String
        Get
            Return _provRes
        End Get
        Set(ByVal Value As String)
            _provRes = Value
        End Set
    End Property
    Public Property telefono() As String
        Get
            Return _telefono
        End Get
        Set(ByVal Value As String)
            _telefono = Value
        End Set
    End Property

    Public Property collegio() As String
        Get
            Return _collegio
        End Get
        Set(ByVal Value As String)
            _collegio = Value
        End Set
    End Property
    Public Property provAlbo() As String
        Get
            Return _provAlbo
        End Get
        Set(ByVal Value As String)
            _provAlbo = Value
        End Set
    End Property
    Public Property dataisc() As String
        Get
            Return _dataIsc
        End Get
        Set(ByVal Value As String)
            _dataIsc = Value
        End Set
    End Property
    Public Property nominativo() As String
        Get
            Return _nominativo
        End Get
        Set(ByVal Value As String)
            _nominativo = Value
        End Set
    End Property
    Public Property cf() As String
        Get
            Return _cf
        End Get
        Set(ByVal Value As String)
            _cf = Value
        End Set
    End Property
    Public Property indContatto() As String
        Get
            Return _indContatto
        End Get
        Set(ByVal Value As String)
            _indContatto = Value
        End Set
    End Property
    Public Property telContatto() As String
        Get
            Return _telContatto
        End Get
        Set(ByVal Value As String)
            _telContatto = Value
        End Set
    End Property
    Public Property fax() As String
        Get
            Return _fax
        End Get
        Set(ByVal Value As String)
            _fax = Value
        End Set
    End Property
    Public Property settori() As DataTable
        Get
            Return _settori
        End Get
        Set(ByVal Value As DataTable)
            _settori = Value
        End Set
    End Property

    Public Property intestRif() As String
        Get
            Return _intestRif
        End Get
        Set(ByVal Value As String)
            _intestRif = Value
        End Set
    End Property
    Public Property nomeRif() As String
        Get
            Return _nomeRif
        End Get
        Set(ByVal Value As String)
            _nomeRif = Value
        End Set
    End Property
    Public Property cognomeRif() As String
        Get
            Return _cognomeRif
        End Get
        Set(ByVal Value As String)
            _cognomeRif = Value
        End Set
    End Property
    Public Property sessoRif() As String
        Get
            Return _sessoRif
        End Get
        Set(ByVal Value As String)
            _sessoRif = Value
        End Set
    End Property


    Public Sub caricaProf()
        Dim sql As String = "select intest, nome, cognome,sesso, luogo_nascita,Comuni.Descrizione as descComune, prov_nascita, cf, convert(char(10),data_nascita,103) as data_nascita,indirizzo, cap, citta, professionisti.provincia, telefono, collegio, provAlbo, num_isc, convert(char(10),data_isc,103) as data_isc,  nominativo, cf_piva, indContatto, telContatto, fax from professionisti left JOIN Comuni ON Professionisti.Luogo_Nascita = Comuni.Codice where id=" & _id
        Dim dr = getDataTable(sql).Rows(0)

        _intest = Utility.sNull(dr("intest"))
        _nome = Utility.sNull(dr("nome"))
        _cognome = Utility.sNull(dr("cognome"))
        _sesso = Utility.sNull(dr("sesso"))
        _dataN = Utility.sNull(dr("data_nascita"))
        _luogoN = Utility.sNull(dr("luogo_nascita"))
        _descComuneN = Utility.sNull(dr("descComune"))
        _provN = Utility.sNull(dr("prov_nascita"))
        _codFisc = Utility.sNull(dr("CF"))
        _indRes = Utility.sNull(dr("indirizzo"))
        _capRes = Utility.sNull(dr("cap"))
        _cittaRes = Utility.sNull(dr("citta"))
        _provRes = Utility.sNull(dr("provincia"))
        _telefono = Utility.sNull(dr("telefono"))
        _collegio = Utility.sNull(dr("collegio"))
        _provAlbo = Trim(Utility.sNull(dr("provalbo")))
        _dataIsc = Utility.sNull(dr("data_isc"))
        _numIsc = Utility.sNull(dr("num_isc"))
        _nominativo = Utility.sNull(dr("nominativo"))
        _cf = Utility.sNull(dr("cf_piva"))
        _indContatto = Utility.sNull(dr("indContatto"))
        _telContatto = Utility.sNull(dr("telcontatto"))
        _fax = Utility.sNull(dr("fax"))
        _settori = getDataTable("SELECT Settori.codice, Settori.descrizione FROM profSettori INNER JOIN " & _
            "Settori ON profSettori.idSettore = Settori.codice WHERE profSettori.idProf =" & _id)

    End Sub

    Public Sub elimina()
        Dim sql As String = "update professionisti set eliminato=1 where id=" & _id
        eseguiSql(sql)
    End Sub
    Public Sub inserisci()


        _id = eseguiSPInsert("insertProf")

        aggiorna()

    End Sub

    Public Sub aggiorna()
        Dim sql As String
        'sql = "update professionisti set intest=@intest, nome =@nome, cognome = @cognome, sesso=@sesso, luogo_nascita =@luogoN, desc_comune=@descComuneN " & _
        '        ", prov_nascita =@provN, data_nascita =@dataN, cf=@codFisc, indirizzo =@indRes, cap =@capRes " & _
        '        ", citta=@cittaRes, provincia =@provRes, telefono=@telefono, collegio=@collegio, provAlbo=@provalbo, data_isc =@dataIsc , num_isc =@numIsc, " & _
        '        " nominativo=@nominativo, cf_piva=@cf, indContatto=@indContatto, telContatto=@telContatto, fax=@fax, utente=@utente, " & _
        '        " intestRif=@intestRif, nomeRif =@nomeRif, cognomeRif = @cognomeRif, sessoRif=@sessoRif where id=" & _id
        sql = "UPDATE Professionisti SET [Intest]=@Intest, [Nome]=@Nome, [Cognome]=@Cognome, [Sesso]=@Sesso, [Luogo_Nascita]=@Luogo_Nascita, [Desc_Comune]=@Desc_Comune, [Prov_Nascita]=@Prov_Nascita, [Data_Nascita]=@Data_Nascita, [CF]=@CF, [Indirizzo]=@Indirizzo, "
        sql += "[Cap]=@Cap, [Citta]=@Citta, [Provincia]=@Provincia, [Telefono]=@Telefono, [Collegio]=@Collegio, [ProvAlbo]=@ProvAlbo, [Data_isc]=@Data_isc, [Num_isc]=@Num_isc, [Nominativo]=@Nominativo, [cf_piva]=@cf_piva, [indContatto]=@indContatto, [telContatto]=@telContatto, "
        sql += "[fax]=@fax WHERE Id = " & _id ', [Intestazione]=@Intestazione, [Note]=@Note "
        'sql += ""
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.Add("@intest", _intest)
        cmd.Parameters.Add("@nome", _nome)
        cmd.Parameters.Add("@cognome", _cognome)
        cmd.Parameters.Add("@sesso", _sesso)
        cmd.Parameters.Add("@Luogo_Nascita", IIf(_luogoN = String.Empty, SqlTypes.SqlString.Null, _luogoN))
        cmd.Parameters.Add("@Desc_Comune", IIf(_descComuneN = String.Empty, SqlTypes.SqlString.Null, _descComuneN))
        cmd.Parameters.Add("@Prov_Nascita", IIf(_provN = String.Empty, SqlTypes.SqlString.Null, _provN))
        cmd.Parameters.Add("@Data_Nascita", IIf(_dataN = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataN)))
        cmd.Parameters.Add("@CF", IIf(_codFisc = String.Empty, SqlTypes.SqlString.Null, _codFisc))
        cmd.Parameters.Add("@Indirizzo", IIf(_indRes = String.Empty, SqlTypes.SqlString.Null, _indRes))
        cmd.Parameters.Add("@Cap", IIf(_capRes = String.Empty, SqlTypes.SqlString.Null, _capRes))
        cmd.Parameters.Add("@Citta", IIf(_cittaRes = String.Empty, SqlTypes.SqlString.Null, _cittaRes))
        cmd.Parameters.Add("@Provincia", IIf(_provRes = String.Empty, SqlTypes.SqlString.Null, _provRes))
        cmd.Parameters.Add("@Telefono", IIf(_telefono = String.Empty, SqlTypes.SqlString.Null, _telefono))
        cmd.Parameters.Add("@Collegio", IIf(_collegio = "", SqlTypes.SqlInt16.Null, _collegio))
        cmd.Parameters.Add("@ProvAlbo", IIf(_provAlbo = String.Empty, SqlTypes.SqlString.Null, _provAlbo))
        cmd.Parameters.Add("@Data_isc", IIf(_dataIsc = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataIsc)))
        cmd.Parameters.Add("@Num_isc", IIf(_numIsc = String.Empty, SqlTypes.SqlString.Null, _numIsc))
        If Not IsNothing(_nominativo) Then
            If (Trim(_nominativo) = String.Empty) Then
                _nominativo = _cognome & " " & _nome
            End If
        End If
        cmd.Parameters.Add("@Nominativo", _nominativo)
        cmd.Parameters.Add("@cf_piva", IIf(_cf = String.Empty, SqlTypes.SqlString.Null, _cf))
        cmd.Parameters.Add("@indContatto", IIf(_indContatto = String.Empty, SqlTypes.SqlString.Null, _indContatto))
        cmd.Parameters.Add("@telContatto", IIf(_telContatto = String.Empty, SqlTypes.SqlString.Null, _telContatto))
        cmd.Parameters.Add("@fax", IIf(_fax = String.Empty, SqlTypes.SqlString.Null, _fax))

        'cmd.Parameters.Add("@Intestazione", _intestRif)
        'cmd.Parameters.Add("@Note", _nomeRif)
        'cmd.Parameters.Add("@cognomeRif", _cognomeRif)
        'cmd.Parameters.Add("@sessoRif", _sessoRif)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()


        eseguiSql("delete from profSettori where idprof =" & _id)

        If Not _settori Is Nothing Then
            Dim dr As DataRow
            For Each dr In _settori.Rows
                eseguiSql("insert into profSettori (idprof, idSettore) values(" & _id & ", " & dr("codice") & ")")
            Next
        End If


    End Sub


End Class

Public Class Professionisti
    Inherits Connessione

    Public Function elenco(ByVal albo As String, ByVal nome As String, ByVal esterni As Boolean) As DataTable
        Dim sql As String
        Dim sqlWhere As String
        sql = "select '' as idana, id, nome, cognome, indirizzo, citta, 0  as iscritto, CONVERT(char(10), Data_Nascita, 103) AS dataN, Num_isc AS matricola from professionisti where eliminato=0 "
        If albo <> "" Then
            sqlWhere = " and collegio = " & albo
        End If
        If nome <> "" Then
            sqlWhere += " and cognome like '" & Replace(nome, "'", "''") & "%' "
        End If
        sql += sqlWhere

        If Not esterni Then
            If HttpContext.Current.Session("albo") = albo Or albo = "" Then
                sql += " union SELECT Anagrafica.id as idana, AlboIscritti.Id, Anagrafica.Nome, Anagrafica.Cognome, Indirizzi.Indirizzo, Indirizzi.Citta, 1 as iscritto, convert(char(10), data_nascita, 103) as dataN, AlboIscritti.numeroIscrizione as matricola " & _
                    "FROM AlboIscritti INNER JOIN Anagrafica ON AlboIscritti.Id_ana = Anagrafica.ID INNER JOIN " & _
                    "Indirizzi ON Anagrafica.ID = Indirizzi.Id_ana " & _
                    "WHERE Indirizzi.TipoIndirizzo = 2 "
                If nome <> "" Then
                    sql += " and  Anagrafica.Cognome like '" & Replace(nome, "'", "''") & "%' "
                End If
            End If
        End If
        sql += " order by cognome, nome"
        Return getDataTable(sql)
    End Function

    Public Function elencoPeriti(ByVal albo As String, ByVal nome As String, ByVal esterni As Boolean) As DataTable
        Dim sql As String
        Dim sqlWhere As String
        sql = "select '' as idana, id, nome, cognome, indirizzo, citta, 0  as iscritto, CONVERT(char(10), Data_Nascita, 103) AS dataN, Num_isc AS matricola from professionisti where eliminato=0 "

        If albo <> "" Then
            sqlWhere = " and collegio = " & albo
        End If

        If nome <> "" Then
            sqlWhere += " and cognome like '" & Replace(nome, "'", "''") & "%' "
        End If
        sql += sqlWhere

        If Not esterni Then
            sql += " union SELECT Anagrafica.id as idana, AnaIscritti.Id, Anagrafica.Nome, Anagrafica.Cognome, Indirizzi.Indirizzo, Indirizzi.Citta, 1 as iscritto, convert(char(10), data_nascita, 103) as dataN, iscrizioni.numero as matricola " & _
                "FROM AnaIscritti INNER JOIN Anagrafica ON AnaIscritti.Id_ana = Anagrafica.ID INNER JOIN " & _
                "Indirizzi ON Anagrafica.ID = Indirizzi.Id_ana INNER JOIN Iscrizioni ON AnaIscritti.Id = Iscrizioni.idIscritto " & _
                "WHERE Indirizzi.TipoIndirizzo = 2 "
            If albo <> "" Then
                sql += " AND Iscrizioni.Albo = " & albo & " and iscrizioni.al is null "
            Else
                sql += " AND Iscrizioni.Albo = " & HttpContext.Current.Session("albo") & " and iscrizioni.al is null "
            End If
            If nome <> "" Then
                sql += " and  Anagrafica.Cognome like '" & Replace(nome, "'", "''") & "%' "
            End If
        End If
        sql += " order by cognome, nome"
        Return getDataTable(sql)
    End Function

    Public Function elencoTipoCollegio() As DataTable
        Return getDataTable("select null as id,  '' as albo union select id, albo from albi")
    End Function
End Class

Public Class cDitte
    Inherits Connessione

    Public Function elenco(ByVal filtro) As DataTable
        Dim sql As String = "select id, ragSoc, indirizzo, citta from ditte where eliminato=0 "
        If filtro <> "" Then
            sql += " and ragsoc like '" & Replace(filtro, "'", "''") & "%'"
        End If
        Return getDataTable(sql)
    End Function

End Class

Public Class cDitta
    Inherits Connessione

    Private _id As String
    Private _ragSoc As String
    Private _Piva As String
    Private _indirizzo As String
    Private _cap As String
    Private _citta As String
    Private _provincia As String
    Private _telefono As String
    Private _fax As String
    Private _provC As String
    Private _dalC As String
    Private _numC As String

    Public Property id() As String
        Get
            Return _id
        End Get
        Set(ByVal Value As String)
            _id = Value
        End Set
    End Property
    Public Property ragSoc() As String
        Get
            Return _ragSoc
        End Get
        Set(ByVal Value As String)
            _ragSoc = Value
        End Set
    End Property
    Public Property Piva() As String
        Get
            Return _Piva
        End Get
        Set(ByVal Value As String)
            _Piva = Value
        End Set
    End Property
    Public Property indirizzo() As String
        Get
            Return _indirizzo
        End Get
        Set(ByVal Value As String)
            _indirizzo = Value
        End Set
    End Property
    Public Property cap() As String
        Get
            Return _cap
        End Get
        Set(ByVal Value As String)
            _cap = Value
        End Set
    End Property
    Public Property citta() As String
        Get
            Return _citta
        End Get
        Set(ByVal Value As String)
            _citta = Value
        End Set
    End Property
    Public Property provincia() As String
        Get
            Return _provincia
        End Get
        Set(ByVal Value As String)
            _provincia = Value
        End Set
    End Property
    Public Property telefono() As String
        Get
            Return _telefono
        End Get
        Set(ByVal Value As String)
            _telefono = Value
        End Set
    End Property
    Public Property fax() As String
        Get
            Return _fax
        End Get
        Set(ByVal Value As String)
            _fax = Value
        End Set
    End Property
    Public Property provC() As String
        Get
            Return _provC
        End Get
        Set(ByVal Value As String)
            _provC = Value
        End Set
    End Property
    Public Property dalC() As String
        Get
            Return _dalC
        End Get
        Set(ByVal Value As String)
            _dalC = Value
        End Set
    End Property
    Public Property numC() As String
        Get
            Return _numC
        End Get
        Set(ByVal Value As String)
            _numC = Value
        End Set
    End Property


    Public Sub carica()
        Dim sql As String = "select *,convert(char(10),dalC,103) as data from ditte where id = " & _id
        Dim dr As DataRow = getDataTable(sql).Rows(0)

        _ragSoc = Utility.sNull(dr("ragSoc"))
        _Piva = Utility.sNull(dr("Piva"))
        _indirizzo = Utility.sNull(dr("indirizzo"))
        _cap = Utility.sNull(dr("cap"))
        _citta = Utility.sNull(dr("citta"))
        _provincia = Utility.sNull(dr("provincia"))
        _telefono = Utility.sNull(dr("telefono"))
        _fax = Utility.sNull(dr("fax"))
        _provC = Utility.sNull(dr("provc"))
        _dalC = Utility.sNull(dr("data"))
        _numC = Utility.sNull(dr("numC"))

    End Sub

    Public Sub elimina()
        Dim sql As String = "update ditte set eliminato=1 where id=" & _id
        eseguiSql(sql)
    End Sub

    Public Sub salva()
        If _id = "" Then
            inserisci()
        Else
            aggiorna()
        End If
    End Sub

    Private Sub inserisci()
        Dim sql As String = "insert into ditte (ragSoc, piva, indirizzo, cap, citta, provincia, telefono, fax, provC, dalC, numC, utente) " & _
            " values (@ragSoc, @piva, @indirizzo, @cap, @citta, @provincia, @telefono, @fax, @provC, @dalC, @numC, @utente)"
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.Add("@ragSoc", _ragSoc)
        cmd.Parameters.Add("@piva", _Piva)
        cmd.Parameters.Add("@indirizzo", _indirizzo)
        cmd.Parameters.Add("@cap", _cap)
        cmd.Parameters.Add("@citta", _citta)
        cmd.Parameters.Add("@provincia", _provincia)
        cmd.Parameters.Add("@telefono", _telefono)
        cmd.Parameters.Add("@fax", _fax)
        cmd.Parameters.Add("@provC", _provC)
        cmd.Parameters.Add("@dalC", IIf(_dalC = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dalC)))
        cmd.Parameters.Add("@numC", _numC)
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Private Sub aggiorna()
        Dim sql As String = "update ditte set ragsoc=@ragSoc, piva= @piva, indirizzo=@indirizzo, cap=@cap, citta=@citta, provincia=@provincia, " & _
            "telefono=@telefono, fax=@fax, provC=@provC, dalC=@dalC, numC=@numC, utente=@utente where id=" & _id
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.Add("@ragSoc", _ragSoc)
        cmd.Parameters.Add("@piva", _Piva)
        cmd.Parameters.Add("@indirizzo", _indirizzo)
        cmd.Parameters.Add("@cap", _cap)
        cmd.Parameters.Add("@citta", _citta)
        cmd.Parameters.Add("@provincia", _provincia)
        cmd.Parameters.Add("@telefono", _telefono)
        cmd.Parameters.Add("@fax", _fax)
        cmd.Parameters.Add("@provC", _provC)
        cmd.Parameters.Add("@dalC", IIf(_dalC = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dalC)))
        cmd.Parameters.Add("@numC", _numC)
        cmd.Parameters.Add("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub


End Class